package com.qububu.db.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.qububu.db.dao.ArticleInfoDao;
import com.qububu.share.article.dto.ArticleInfoDto;
import com.qububu.utils.ParamConverter;
import com.qububu.utils.SystemUtil;

/**
 * @功能 与文章数据有关的数据库操作
 * 
 * @author LH
 * */
@Repository
public class ArticleInfoDaoImpl implements ArticleInfoDao{

	private JdbcTemplate jdbcTemplate;
	
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	/**
	 * @功能 添加文章的部分内容
	 * @param articleInfoDto 文章信息
	 * */
	public void doAddArticle(final ArticleInfoDto articleInfo) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		final String sqlToArticleInfo = "insert into article_info(article_title, art_summary_text, art_content, art_summary_pic_url, article_create_dttm, article_create_ip, article_type, user_id) values(?, ?, ?, ?, ?, ?, ?, ?)";
		
		jdbcTemplate.update(
				new PreparedStatementCreator() {
		            public PreparedStatement createPreparedStatement(Connection con)
		                    throws SQLException {
		                PreparedStatement ps = con.prepareStatement(sqlToArticleInfo, Statement.RETURN_GENERATED_KEYS);
		                int z = 0;
		                ps.setString(++z, articleInfo.getArticleTitle());
		                ps.setString(++z, articleInfo.getArticleSummaryText());
		                ps.setString(++z, articleInfo.getArticleText());
		                ps.setString(++z, null);
		                ps.setInt(++z, articleInfo.getArticleCreateDttm());
		                ps.setString(++z, articleInfo.getArticleCreateIp());
		                ps.setInt(++z, articleInfo.getArticleType());
		                ps.setString(++z, articleInfo.getUserId());
		                return ps;
		            }
        		}, keyHolder);
		
		Long articleId = keyHolder.getKey().longValue();
		
		if (articleId == 0){
			articleId = jdbcTemplate.queryForLong("SELECT article_id FROM article_info WHERE user_id=? and article_create_dttm=?",
															new Object[]{articleInfo.getUserId(), articleInfo.getArticleCreateDttm()}
														);
		}
		
		String sqlToArticleInfo2 = "insert into article_detail_msg(msg_title, msg_content, msg_create_name, msg_create_ip, msg_create_dttm, msg_summary_pic_url, msg_type, article_id, parent_msg_id, user_id) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		Object[] articleDetailInfoValues = {
						articleInfo.getArticleTitle(),
						articleInfo.getArticleText(),
						articleInfo.getUserId(), // 有问题,应该存入名字
						articleInfo.getArticleCreateIp(),
						articleInfo.getArticleCreateDttm(),
						articleInfo.getArtSummaryPicUrl(),
						articleInfo.getArticleType(),
						articleId,
						0,
						articleInfo.getUserId()
						};
		
		jdbcTemplate.update(sqlToArticleInfo2, articleDetailInfoValues);
		
	}
	
	/**
	 * @功能 查询文章列表
	 * @author LH
	 * @param articleInfo 文章信息
	 * @return articleList 文章的列表
	 * */
	public ArrayList<ArticleInfoDto> doQueryArticle() {
		ArrayList<ArticleInfoDto> articleList = new ArrayList<ArticleInfoDto>();
		String sqlToArticleInfo = "select * from article_info order by article_create_dttm desc limit 0,50";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sqlToArticleInfo);
		
		for (Map<String, Object> elem : list){
			ArticleInfoDto articleInfo = new ArticleInfoDto((Long) elem.get("article_id"), (Long) elem.get("user_id") );
			
			articleInfo.setArticleTitle(String.valueOf(elem.get("article_title")));
			articleInfo.setArticleText(String.valueOf(elem.get("art_summary_text")));
			articleInfo.setArtSummaryPicUrl(String.valueOf(elem.get("art_summary_pic_url")));
			articleInfo.setArticleCreateDttm(ParamConverter.convertToInteger(String.valueOf(elem.get("article_create_dttm"))));
			articleInfo.setArticleType(ParamConverter.convertToInteger(String.valueOf(elem.get("article_type"))));
			
			articleList.add(articleInfo);
		}
		return articleList;
	}
	
	/**
	 * @功能 查询文章内容
	 * @author LH
	 * @param articleInfo 文章信息
	 * @return articleInfo 文章内容
	 * */
	public ArticleInfoDto doQueryDetailArticle(String articleId) {
		//解密
		articleId = SystemUtil.decrypt(articleId);
		
		String sqlToArticleInfo = "select * from article_detail_msg where article_id=? and parent_msg_id=?";
		Object[] values = {articleId, 0};
		Map<String, Object> elem = jdbcTemplate.queryForMap(sqlToArticleInfo, values);
		
		ArticleInfoDto articleInfo = new ArticleInfoDto((Long) elem.get("msg_id"), (Long) elem.get("user_id") );
		articleInfo.setArticleTitle(String.valueOf(elem.get("msg_title")));
		articleInfo.setArticleText(String.valueOf(elem.get("msg_content")));
		articleInfo.setArticleCreateIp(String.valueOf(elem.get("msg_create_ip")));
		articleInfo.setArticleCreateDttm(ParamConverter.convertToInteger(String.valueOf(elem.get("msg_create_dttm"))));
		articleInfo.setArtSummaryPicUrl(String.valueOf(elem.get("art_summary_pic_url")));
		articleInfo.setArticleType(ParamConverter.convertToInteger(String.valueOf(elem.get("msg_type"))));
		
		return articleInfo;
	}
	
	/**
	 * @功能 删除文章
	 * @author LH
	 * @param article_id 文章的id
	 * */
	public boolean doDeleteArticle(String articleId){
		//解密
		articleId = SystemUtil.decrypt(articleId);
		
		String fromArticleInfo = "delete from article_info where article_id=?";
		jdbcTemplate.update(fromArticleInfo, articleId);
		
		String fromMessageInfo = "delete from article_detail_msg where article_id=?";
		jdbcTemplate.update(fromMessageInfo, articleId);
		
		return true;
	}

}